﻿--store kiem tra duoc dang tin hay ko?

create proc spKiemTraDangTin
	@user_id int,
	@flag nvarchar(3) output
	
as
begin
	declare @manhatuyendung int
	--lay ma nha tuyen dung
	select @manhatuyendung = manhatuyendung
	from nhatuyendung
	where user_id = @user_id
	--xem nha tuyen dung co dong phi
	declare @dadongphi int,@ngaydongphi datetime
	select @dadongphi=count(*),@ngaydongphi = ngayketthuc
	from nhatuyendung_phi
	where manhatuyendung = @manhatuyendung and tinhtrang = 1
	
	if(@dadongphi = 0)
	begin
		set @flag = 0
		return
	end
	
	else
	begin
		declare @sotin int, @tongsotindadang int
		--lay so tin co the dang cua phi
		select @sotin = soluongtin
		from phi
		where manhatuyendung = @manhatuyendung and tinhtrang = 1
		
		--lay so tin da dang cua nha tuyen dung
		select @tongsotindadang = count(*)
		from congviec
		where ngaydang >= @ngaydongphi and manhatuyendung = @manhatuyendung
		
		if(@tongsotindadang >= @sotin)
		begin
			set @flag = 0
			return
		end
			
	end
	set @flag = 1
	return
end






--2 cai moi
ALTER proc spKiemTraDongPhiHopLe
	@userid int,
	@Flag nvarchar(5) output
as
begin

	declare @manhatuyendung int
	select @manhatuyendung = manhatuyendung 
	from nhatuyendung
	where user_id = @userid

	declare @dadongphi int
	set @dadongphi = 0
	select @dadongphi = count(*) 
	from nhatuyendung_phi ntdp left join phi p on ntdp.maphi = p.maphi
	where tinhtrang = 1 and manhatuyendung = @manhatuyendung and gia <> 0
	
	if(@dadongphi = 1)
		Set @Flag = '0'
	if(@dadongphi = 0)
		Set @Flag = '1'
end


ALTER proc spNhaTuyenDungDongPhi
	@userid int,
	@maphi int,
	@ngaybatdau datetime,
	@hinhthucthanhtoan nvarchar(100),
	@Flag nvarchar(10) output
as
begin
	begin tran
		
		
		declare @manhatuyendung int
		select @manhatuyendung = manhatuyendung 
		from nhatuyendung
		where user_id = @userid
		
		
		--thiet lap lai loai phi "mien phi" cho nha tuyen dung
		declare @phimienphi int
		set @phimienphi = 0
		select @phimienphi = count(*) 
		from nhatuyendung_phi ntdp left join phi p on ntdp.maphi = p.maphi
		where tinhtrang = 1 and manhatuyendung = @manhatuyendung and gia = 0
		
		--kiem tra phi "mien phi"
		if(@phimienphi = 1)
		begin
			update nhatuyendung_phi
			set tinhtrang = 0
			where manhatuyendung = @manhatuyendung and tinhtrang = 1
		end
		
		
			
		declare @ngayketthuc datetime,@songay int
		--lay thoi han cua phi
		select @songay = thoihan 
		from phi
		where maphi = @maphi
		--tinh ngay ket thuc
		set @ngayketthuc = @ngaybatdau + @songay
		
		--kiem tra ton tai
		declare @tontai int
		select @tontai = count(*) from nhatuyendung_phi
		where manhatuyendung = @manhatuyendung and maphi = @maphi

		if(@tontai = 1)
		begin 
			update nhatuyendung_phi
			set ngaybatdau = @ngaybatdau,
			ngayketthuc = @ngayketthuc,
			tinhtrang = 1,
			hinhthucthanhtoan = @hinhthucthanhtoan
			where manhatuyendung = @manhatuyendung and maphi = @maphi
			if(@@error <> 0)
			begin
				
				Set @Flag = '0'
				rollback tran
				return
			end
		end		
		
		
		else
		begin
			insert into nhatuyendung_phi(manhatuyendung,maphi,ngaybatdau,ngayketthuc,tinhtrang,hinhthucthanhtoan)
			values(@manhatuyendung,@maphi,@ngaybatdau,@ngayketthuc,1,@hinhthucthanhtoan)
		end
		if(@@error <> 0)
        begin
			
			Set @Flag = '0'
			rollback tran
			return
		end
		Set @Flag = '1'
		commit tran
		return 
		
	
end





create proc spLayDanhSachPhi
as
begin
	select MaPhi,Ten
	from Phi
	where doituong = 0 and maphi <> 1
end


create proc spKiemTraPhiHetHan
as
begin
	declare @cur cursor
	declare @maphi int, @manhatuyendung int, @ngayketthuc datetime
	set @cur = cursor for select maphi,manhatuyendung,ngayketthuc
				from nhatuyendung_phi
	
	open @cur
	fetch next from @cur into @maphi,@manhatuyendung,@ngayketthuc
	while @@fetch_status = 0
	begin
		update nhatuyendung_phi
		set tinhtrang = 0
		where ngayketthuc < getdate()
		fetch next from @cur into @maphi,@manhatuyendung,@ngayketthuc
	end	
	close @cur
	deallocate @cur
	
end




exec spKiemTraDongPhiHopLe 13

exec spNhaTuyenDungDongPhi 2,2,'2010/12/12',N'Chuyển Khoản'
select * from nhatuyendung_phi

select * from phi

exec spKiemTraPhiHetHan
select * from nhatuyendung_phi